// 推荐算法相关数据库查询
import { getDB } from './index.js'

/**
 * 获取用户最近浏览记录的分类统计
 * @param {string} userId 用户ID
 * @param {number} limit 限制记录数，默认100
 * @returns {Array<{categoryId: number, count: number}>} 分类ID和浏览次数的数组，按次数降序
 */
export function getUserCategoryStats(userId, limit = 100) {
  const db = getDB()
  
  // 先获取用户最近浏览的图片ID（去重，每个图片只算一次）
  const recentHistoryStmt = db.prepare(`
    SELECT DISTINCT h.imageId
    FROM ViewHistory h
    INNER JOIN Image i ON h.imageId = i.id
    WHERE h.userId = ? AND i.status = 1 AND i.categoryId IS NOT NULL
    ORDER BY h.viewedAt DESC
    LIMIT ?
  `)
  
  const recentImageIds = recentHistoryStmt.all(userId, limit).map(row => row.imageId)
  
  if (recentImageIds.length === 0) {
    return []
  }
  
  // 统计这些图片的分类分布
  const placeholders = recentImageIds.map(() => '?').join(',')
  const categoryStatsStmt = db.prepare(`
    SELECT 
      i.categoryId,
      COUNT(*) as count
    FROM Image i
    WHERE i.id IN (${placeholders}) AND i.status = 1 AND i.categoryId IS NOT NULL
    GROUP BY i.categoryId
    ORDER BY count DESC
  `)
  
  const stats = categoryStatsStmt.all(...recentImageIds)
  
  return stats.map(row => ({
    categoryId: row.categoryId,
    count: row.count
  }))
}

/**
 * 根据分类ID列表获取推荐图片
 * @param {Array<number>} categoryIds 分类ID数组（按优先级排序）
 * @param {number} limit 每类获取的图片数量
 * @param {Array<number>} excludeImageIds 排除的图片ID数组（已浏览过的）
 * @returns {Array} 推荐图片列表
 */
export function getRecommendedImagesByCategories(categoryIds, limit = 20, excludeImageIds = []) {
  const db = getDB()
  
  if (!categoryIds || categoryIds.length === 0) {
    return []
  }
  
  const allImages = []
  const imagesPerCategory = Math.ceil(limit / categoryIds.length)
  
  for (const categoryId of categoryIds) {
    let whereClause = 'WHERE i.status = 1 AND i.categoryId = ?'
    const params = [categoryId]
    
    // 排除已浏览的图片
    if (excludeImageIds.length > 0) {
      const placeholders = excludeImageIds.map(() => '?').join(',')
      whereClause += ` AND i.id NOT IN (${placeholders})`
      params.push(...excludeImageIds)
    }
    
    const stmt = db.prepare(`
      SELECT 
        i.*,
        c.id as category_id,
        c.name as category_name,
        c.slug as category_slug
      FROM Image i
      LEFT JOIN Category c ON i.categoryId = c.id
      ${whereClause}
      ORDER BY i.createdAt DESC
      LIMIT ?
    `)
    
    const images = stmt.all(...params, imagesPerCategory).map(row => ({
      id: row.id,
      title: row.title,
      url: row.url,
      thumbnail: row.thumbnail,
      width: row.width,
      height: row.height,
      size: row.size,
      categoryId: row.categoryId,
      status: row.status,
      views: row.views,
      likes: row.likes,
      createdAt: row.createdAt,
      updatedAt: row.updatedAt,
      category: row.category_id ? {
        id: row.category_id,
        name: row.category_name,
        slug: row.category_slug
      } : null
    }))
    
    allImages.push(...images)
  }
  
  // 随机打乱顺序
  return shuffleArray(allImages).slice(0, limit)
}

/**
 * 获取用户浏览过的图片ID列表（用于排除）
 * @param {string} userId 用户ID
 * @param {number} limit 限制数量
 * @returns {Array<number>} 图片ID数组
 */
export function getUserViewedImageIds(userId, limit = 1000) {
  const db = getDB()
  
  const stmt = db.prepare(`
    SELECT DISTINCT h.imageId
    FROM ViewHistory h
    WHERE h.userId = ?
    LIMIT ?
  `)
  
  const rows = stmt.all(userId, limit)
  return rows.map(row => row.imageId)
}

/**
 * 随机打乱数组（Fisher-Yates算法）
 * @param {Array} array 要打乱的数组
 * @returns {Array} 打乱后的新数组
 */
function shuffleArray(array) {
  const shuffled = [...array]
  for (let i = shuffled.length - 1; i > 0; i--) {
    const j = Math.floor(Math.random() * (i + 1));
    [shuffled[i], shuffled[j]] = [shuffled[j], shuffled[i]]
  }
  return shuffled
}

/**
 * 获取用户推荐图片
 * @param {string} userId 用户ID
 * @param {number} limit 推荐数量
 * @returns {Array} 推荐图片列表
 */
export function getUserRecommendedImages(userId, limit = 20) {
  // 获取用户分类偏好
  const categoryStats = getUserCategoryStats(userId, 100)
  
  if (categoryStats.length === 0) {
    // 如果用户没有浏览历史，返回空数组（使用默认推荐）
    return []
  }
  
  // 提取分类ID列表（按浏览次数排序）
  const categoryIds = categoryStats.map(stat => stat.categoryId)
  
  // 获取用户已浏览的图片ID（排除这些图片）
  const viewedImageIds = getUserViewedImageIds(userId, 1000)
  
  // 根据分类偏好获取推荐图片
  const recommendedImages = getRecommendedImagesByCategories(categoryIds, limit, viewedImageIds)
  
  // 如果推荐图片数量不足，补充其他分类的最新图片
  if (recommendedImages.length < limit) {
    const db = getDB()
    const remainingCount = limit - recommendedImages.length
    const excludeIds = [...viewedImageIds, ...recommendedImages.map(img => img.id)]
    
    let whereClause = 'WHERE i.status = 1'
    const params = []
    
    if (excludeIds.length > 0) {
      const placeholders = excludeIds.map(() => '?').join(',')
      whereClause += ` AND i.id NOT IN (${placeholders})`
      params.push(...excludeIds)
    }
    
    // 排除已推荐分类（避免重复）
    if (categoryIds.length > 0) {
      const categoryPlaceholders = categoryIds.map(() => '?').join(',')
      whereClause += ` AND i.categoryId NOT IN (${categoryPlaceholders})`
      params.push(...categoryIds)
    }
    
    const stmt = db.prepare(`
      SELECT 
        i.*,
        c.id as category_id,
        c.name as category_name,
        c.slug as category_slug
      FROM Image i
      LEFT JOIN Category c ON i.categoryId = c.id
      ${whereClause}
      ORDER BY i.createdAt DESC
      LIMIT ?
    `)
    
    const additionalImages = stmt.all(...params, remainingCount).map(row => ({
      id: row.id,
      title: row.title,
      url: row.url,
      thumbnail: row.thumbnail,
      width: row.width,
      height: row.height,
      size: row.size,
      categoryId: row.categoryId,
      status: row.status,
      views: row.views,
      likes: row.likes,
      createdAt: row.createdAt,
      updatedAt: row.updatedAt,
      category: row.category_id ? {
        id: row.category_id,
        name: row.category_name,
        slug: row.category_slug
      } : null
    }))
    
    recommendedImages.push(...additionalImages)
  }
  
  // 最终随机打乱并限制数量
  return shuffleArray(recommendedImages).slice(0, limit)
}

